#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2022-04-28 13:47
# @Author  : 徐昌龙
# @FileName: dict_db.py
"""
根据逻辑处理需要，提供数据支持
"""
import pymysql
import hashlib


class Dict:
    @staticmethod
    def change_passwd(passwd):
        hash = hashlib.sha256()
        hash.update(passwd.encode())
        return hash.hexdigest()

    def __init__(self):
        self.kwargs = {
            "host": "192.168.125.128",
            "port": 3306,
            "user": "root",
            "passwd": "123456",
            "database": "dict",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**self.kwargs)
        self.cur = self.db.cursor()

    def close(self):
        self.cur.close()
        self.db.close()

    # 　注册验证
    def register(self, name, passwd):
        passwd = Dict.change_passwd(passwd)
        sql = "insert into user (name,passwd) values (%s,%s);"
        try:
            self.cur.execute(sql, [name, passwd])
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    def login(self, name, passwd):
        sql = "select id from user where binary name=%s and passwd=%s;"  # 区分大小写
        self.cur.execute(sql, [name, passwd])
        if self.cur.fetchone():
            return True
        return False

    def query(self, word):
        sql = "select mean from word where word=%s;"
        self.cur.execute(sql, [word])
        result = self.cur.fetchone()
        if result:
            return result[0]
        else:
            return False

    def insert_hist(self, name, word):
        sql = "select id from user where name=%s;"
        self.cur.execute(sql, [name])
        user_id = self.cur.fetchone()[0]
        sql = "select id from word where word=%s;"
        self.cur.execute(sql, [word])
        word_id = self.cur.fetchone()[0]
        try:
            sql = "insert into hist (user_id, word_id) values (%s, %s);"
            self.cur.execute(sql, [user_id, word_id])
            self.db.commit()
        except Exception as e:
            print(e)
            self.db.rollback()

    def hist(self, name):
        sql = "select name, word, mean, time from " \
              "user inner join hist " \
              "on user.id=hist.user_id " \
              "inner join word " \
              "on word.id=hist.word_id " \
              "where name=%s " \
              "order by time desc " \
              "limit 10;"
        self.cur.execute(sql, [name])
        return self.cur.fetchall()


if __name__ == '__main__':
    dict = Dict()
    dict.hist("xu")
